{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 导入数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>订单号</th>\n",
       "      <th>产品码</th>\n",
       "      <th>消费日期</th>\n",
       "      <th>产品说明</th>\n",
       "      <th>数量</th>\n",
       "      <th>单价</th>\n",
       "      <th>用户码</th>\n",
       "      <th>城市</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>536374</td>\n",
       "      <td>21258</td>\n",
       "      <td>6/1/2020 9:09</td>\n",
       "      <td>五彩玫瑰五支装</td>\n",
       "      <td>32</td>\n",
       "      <td>10.95</td>\n",
       "      <td>15100</td>\n",
       "      <td>北京</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>536376</td>\n",
       "      <td>22114</td>\n",
       "      <td>6/1/2020 9:32</td>\n",
       "      <td>茉莉花白色25枝</td>\n",
       "      <td>48</td>\n",
       "      <td>3.45</td>\n",
       "      <td>15291</td>\n",
       "      <td>上海</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>536376</td>\n",
       "      <td>21733</td>\n",
       "      <td>6/1/2020 9:32</td>\n",
       "      <td>教师节向日葵3枝尤加利5枝</td>\n",
       "      <td>64</td>\n",
       "      <td>2.55</td>\n",
       "      <td>15291</td>\n",
       "      <td>上海</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>536378</td>\n",
       "      <td>22386</td>\n",
       "      <td>6/1/2020 9:37</td>\n",
       "      <td>百合粉色10花苞</td>\n",
       "      <td>10</td>\n",
       "      <td>1.95</td>\n",
       "      <td>14688</td>\n",
       "      <td>北京</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>536378</td>\n",
       "      <td>85099C</td>\n",
       "      <td>6/1/2020 9:37</td>\n",
       "      <td>橙黄香槟色康乃馨</td>\n",
       "      <td>10</td>\n",
       "      <td>1.95</td>\n",
       "      <td>14688</td>\n",
       "      <td>北京</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      订单号     产品码           消费日期           产品说明  数量     单价    用户码  城市\n",
       "0  536374   21258  6/1/2020 9:09        五彩玫瑰五支装  32  10.95  15100  北京\n",
       "1  536376   22114  6/1/2020 9:32       茉莉花白色25枝  48   3.45  15291  上海\n",
       "2  536376   21733  6/1/2020 9:32  教师节向日葵3枝尤加利5枝  64   2.55  15291  上海\n",
       "3  536378   22386  6/1/2020 9:37       百合粉色10花苞  10   1.95  14688  北京\n",
       "4  536378  85099C  6/1/2020 9:37       橙黄香槟色康乃馨  10   1.95  14688  北京"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd # 导入Pandas\n",
    "df_sales = pd.read_csv('易速鲜花订单记录.csv') # 载入数据\n",
    "df_sales.head() # 显示头几行数据"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 整理日期格式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "日期范围(格式转化前): 1/1/2021 10:11 ~ 9/9/2020 9:20\n",
      "日期范围(格式转化后): 2020-06-01 09:09:00 ~ 2021-06-09 12:31:00\n"
     ]
    }
   ],
   "source": [
    "df_sales = pd.read_csv('易速鲜花订单记录.csv') # 载入数据\n",
    "print('日期范围(格式转化前): %s ~ %s' % (df_sales['消费日期'].min(), df_sales['消费日期'].max())) # 显示日期范围(格式转换前)\n",
    "df_sales['消费日期'] = pd.to_datetime(df_sales['消费日期']) # 转换日期格式\n",
    "print('日期范围(格式转化后): %s ~ %s' % (df_sales['消费日期'].min(), df_sales['消费日期'].max())) # 显示日期范围"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "日期范围(删除不完整的月份): 2020-06-01 09:09:00 ~ 2021-05-31 17:39:00\n"
     ]
    }
   ],
   "source": [
    "df_sales = df_sales.loc[df_sales['消费日期'] < '2021-06-01'] # 只保留整月数据\n",
    "print('日期范围(删除不完整的月份): %s ~ %s' % (df_sales['消费日期'].min(), df_sales['消费日期'].max())) # 显示日期范围"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 数据可视化"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Figure size 1200x600 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import matplotlib.pyplot as plt #导入Matplotlib的pyplot模块\n",
    "#构建月度的订单数的DataFrame\n",
    "df_orders_monthly = df_sales.set_index('消费日期')['订单号'].resample('M').nunique()\n",
    "#设定绘图的画布\n",
    "ax = pd.DataFrame(df_orders_monthly.values).plot(grid=True,figsize=(12,6),legend=False)\n",
    "ax.set_xlabel('月份') # X轴label\n",
    "ax.set_ylabel('订单数') # Y轴Label\n",
    "ax.set_title('月度订单数') # 图题\n",
    "#设定X轴月份显示格式\n",
    "plt.xticks(\n",
    "    range(len(df_orders_monthly.index)), \n",
    "    [x.strftime('%m.%Y') for x in df_orders_monthly.index], \n",
    "    rotation=45)\n",
    "plt.show() # 绘图"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 数据清洗"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sales = df_sales.drop_duplicates() #删除重复的数据行"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 特征工程"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>订单号</th>\n",
       "      <th>产品码</th>\n",
       "      <th>消费日期</th>\n",
       "      <th>产品说明</th>\n",
       "      <th>数量</th>\n",
       "      <th>单价</th>\n",
       "      <th>用户码</th>\n",
       "      <th>城市</th>\n",
       "      <th>总价</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>536374</td>\n",
       "      <td>21258</td>\n",
       "      <td>2020-06-01 09:09:00</td>\n",
       "      <td>五彩玫瑰五支装</td>\n",
       "      <td>32</td>\n",
       "      <td>10.95</td>\n",
       "      <td>15100</td>\n",
       "      <td>北京</td>\n",
       "      <td>350.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>536376</td>\n",
       "      <td>22114</td>\n",
       "      <td>2020-06-01 09:32:00</td>\n",
       "      <td>茉莉花白色25枝</td>\n",
       "      <td>48</td>\n",
       "      <td>3.45</td>\n",
       "      <td>15291</td>\n",
       "      <td>上海</td>\n",
       "      <td>165.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>536376</td>\n",
       "      <td>21733</td>\n",
       "      <td>2020-06-01 09:32:00</td>\n",
       "      <td>教师节向日葵3枝尤加利5枝</td>\n",
       "      <td>64</td>\n",
       "      <td>2.55</td>\n",
       "      <td>15291</td>\n",
       "      <td>上海</td>\n",
       "      <td>163.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>536378</td>\n",
       "      <td>22386</td>\n",
       "      <td>2020-06-01 09:37:00</td>\n",
       "      <td>百合粉色10花苞</td>\n",
       "      <td>10</td>\n",
       "      <td>1.95</td>\n",
       "      <td>14688</td>\n",
       "      <td>北京</td>\n",
       "      <td>19.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>536378</td>\n",
       "      <td>85099C</td>\n",
       "      <td>2020-06-01 09:37:00</td>\n",
       "      <td>橙黄香槟色康乃馨</td>\n",
       "      <td>10</td>\n",
       "      <td>1.95</td>\n",
       "      <td>14688</td>\n",
       "      <td>北京</td>\n",
       "      <td>19.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      订单号     产品码                消费日期           产品说明  数量     单价    用户码  城市  \\\n",
       "0  536374   21258 2020-06-01 09:09:00        五彩玫瑰五支装  32  10.95  15100  北京   \n",
       "1  536376   22114 2020-06-01 09:32:00       茉莉花白色25枝  48   3.45  15291  上海   \n",
       "2  536376   21733 2020-06-01 09:32:00  教师节向日葵3枝尤加利5枝  64   2.55  15291  上海   \n",
       "3  536378   22386 2020-06-01 09:37:00       百合粉色10花苞  10   1.95  14688  北京   \n",
       "4  536378  85099C 2020-06-01 09:37:00       橙黄香槟色康乃馨  10   1.95  14688  北京   \n",
       "\n",
       "      总价  \n",
       "0  350.4  \n",
       "1  165.6  \n",
       "2  163.2  \n",
       "3   19.5  \n",
       "4   19.5  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sales['总价'] = df_sales['数量'] * df_sales['单价'] #计算每单的总价\n",
    "df_sales.head() #显示头几行数据  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 构建User用户表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>订单号</th>\n",
       "      <th>产品码</th>\n",
       "      <th>消费日期</th>\n",
       "      <th>产品说明</th>\n",
       "      <th>数量</th>\n",
       "      <th>单价</th>\n",
       "      <th>用户码</th>\n",
       "      <th>城市</th>\n",
       "      <th>总价</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>536374</td>\n",
       "      <td>21258</td>\n",
       "      <td>2020-06-01 09:09:00</td>\n",
       "      <td>五彩玫瑰五支装</td>\n",
       "      <td>32</td>\n",
       "      <td>10.95</td>\n",
       "      <td>15100</td>\n",
       "      <td>北京</td>\n",
       "      <td>350.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>536376</td>\n",
       "      <td>22114</td>\n",
       "      <td>2020-06-01 09:32:00</td>\n",
       "      <td>茉莉花白色25枝</td>\n",
       "      <td>48</td>\n",
       "      <td>3.45</td>\n",
       "      <td>15291</td>\n",
       "      <td>上海</td>\n",
       "      <td>165.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>536376</td>\n",
       "      <td>21733</td>\n",
       "      <td>2020-06-01 09:32:00</td>\n",
       "      <td>教师节向日葵3枝尤加利5枝</td>\n",
       "      <td>64</td>\n",
       "      <td>2.55</td>\n",
       "      <td>15291</td>\n",
       "      <td>上海</td>\n",
       "      <td>163.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>536378</td>\n",
       "      <td>22386</td>\n",
       "      <td>2020-06-01 09:37:00</td>\n",
       "      <td>百合粉色10花苞</td>\n",
       "      <td>10</td>\n",
       "      <td>1.95</td>\n",
       "      <td>14688</td>\n",
       "      <td>北京</td>\n",
       "      <td>19.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>536378</td>\n",
       "      <td>85099C</td>\n",
       "      <td>2020-06-01 09:37:00</td>\n",
       "      <td>橙黄香槟色康乃馨</td>\n",
       "      <td>10</td>\n",
       "      <td>1.95</td>\n",
       "      <td>14688</td>\n",
       "      <td>北京</td>\n",
       "      <td>19.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14564</th>\n",
       "      <td>545190</td>\n",
       "      <td>22937</td>\n",
       "      <td>2020-08-29 15:32:00</td>\n",
       "      <td>产品说明掩码</td>\n",
       "      <td>6</td>\n",
       "      <td>18.00</td>\n",
       "      <td>15656</td>\n",
       "      <td>苏州</td>\n",
       "      <td>108.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14565</th>\n",
       "      <td>545190</td>\n",
       "      <td>22722</td>\n",
       "      <td>2020-08-29 15:32:00</td>\n",
       "      <td>产品说明掩码</td>\n",
       "      <td>4</td>\n",
       "      <td>39.50</td>\n",
       "      <td>15656</td>\n",
       "      <td>苏州</td>\n",
       "      <td>158.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14566</th>\n",
       "      <td>545190</td>\n",
       "      <td>22457</td>\n",
       "      <td>2020-08-29 15:32:00</td>\n",
       "      <td>产品说明掩码</td>\n",
       "      <td>60</td>\n",
       "      <td>3.00</td>\n",
       "      <td>15656</td>\n",
       "      <td>苏州</td>\n",
       "      <td>180.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14567</th>\n",
       "      <td>545190</td>\n",
       "      <td>22464</td>\n",
       "      <td>2020-08-29 15:32:00</td>\n",
       "      <td>产品说明掩码</td>\n",
       "      <td>12</td>\n",
       "      <td>25.00</td>\n",
       "      <td>15656</td>\n",
       "      <td>苏州</td>\n",
       "      <td>300.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14568</th>\n",
       "      <td>545190</td>\n",
       "      <td>22423</td>\n",
       "      <td>2020-08-29 15:32:00</td>\n",
       "      <td>产品说明掩码</td>\n",
       "      <td>1</td>\n",
       "      <td>12.75</td>\n",
       "      <td>15656</td>\n",
       "      <td>苏州</td>\n",
       "      <td>12.75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14569 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          订单号     产品码                消费日期           产品说明  数量     单价    用户码  \\\n",
       "0      536374   21258 2020-06-01 09:09:00        五彩玫瑰五支装  32  10.95  15100   \n",
       "1      536376   22114 2020-06-01 09:32:00       茉莉花白色25枝  48   3.45  15291   \n",
       "2      536376   21733 2020-06-01 09:32:00  教师节向日葵3枝尤加利5枝  64   2.55  15291   \n",
       "3      536378   22386 2020-06-01 09:37:00       百合粉色10花苞  10   1.95  14688   \n",
       "4      536378  85099C 2020-06-01 09:37:00       橙黄香槟色康乃馨  10   1.95  14688   \n",
       "...       ...     ...                 ...            ...  ..    ...    ...   \n",
       "14564  545190   22937 2020-08-29 15:32:00         产品说明掩码   6  18.00  15656   \n",
       "14565  545190   22722 2020-08-29 15:32:00         产品说明掩码   4  39.50  15656   \n",
       "14566  545190   22457 2020-08-29 15:32:00         产品说明掩码  60   3.00  15656   \n",
       "14567  545190   22464 2020-08-29 15:32:00         产品说明掩码  12  25.00  15656   \n",
       "14568  545190   22423 2020-08-29 15:32:00         产品说明掩码   1  12.75  15656   \n",
       "\n",
       "       城市      总价  \n",
       "0      北京  350.40  \n",
       "1      上海  165.60  \n",
       "2      上海  163.20  \n",
       "3      北京   19.50  \n",
       "4      北京   19.50  \n",
       "...    ..     ...  \n",
       "14564  苏州  108.00  \n",
       "14565  苏州  158.00  \n",
       "14566  苏州  180.00  \n",
       "14567  苏州  300.00  \n",
       "14568  苏州   12.75  \n",
       "\n",
       "[14569 rows x 9 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sales_3m = df_sales[(df_sales.消费日期 > '2020-06-01') & (df_sales.消费日期 <= '2020-08-30')] #构建仅含头三个月数据的数据集\n",
    "df_sales_3m.reset_index(drop=True) #重置索引"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>用户码</th>\n",
       "      <th>R值</th>\n",
       "      <th>F值</th>\n",
       "      <th>M值</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>15100</td>\n",
       "      <td>45</td>\n",
       "      <td>6</td>\n",
       "      <td>635.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15291</td>\n",
       "      <td>35</td>\n",
       "      <td>35</td>\n",
       "      <td>1329.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>14688</td>\n",
       "      <td>6</td>\n",
       "      <td>85</td>\n",
       "      <td>1472.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>15311</td>\n",
       "      <td>5</td>\n",
       "      <td>715</td>\n",
       "      <td>12711.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>15862</td>\n",
       "      <td>89</td>\n",
       "      <td>64</td>\n",
       "      <td>354.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>365</th>\n",
       "      <td>15951</td>\n",
       "      <td>1</td>\n",
       "      <td>22</td>\n",
       "      <td>375.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>366</th>\n",
       "      <td>14745</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>240.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>367</th>\n",
       "      <td>15724</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>103.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>368</th>\n",
       "      <td>15874</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>584.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>369</th>\n",
       "      <td>15656</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>920.35</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>370 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       用户码  R值   F值        M值\n",
       "0    15100  45    6    635.10\n",
       "1    15291  35   35   1329.95\n",
       "2    14688   6   85   1472.28\n",
       "3    15311   5  715  12711.66\n",
       "4    15862  89   64    354.23\n",
       "..     ...  ..  ...       ...\n",
       "365  15951   1   22    375.17\n",
       "366  14745   1    7    240.60\n",
       "367  15724   0    5    103.65\n",
       "368  15874   0    5    584.35\n",
       "369  15656   0   15    920.35\n",
       "\n",
       "[370 rows x 4 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_user = pd.DataFrame(df_sales['用户码'].unique()) #生成以用户码为主键的结构\n",
    "df_user.columns = ['用户码'] #设定字段名\n",
    "df_user.head() #显示头几行数据\n",
    "\n",
    "df_R_value = df_sales_3m.groupby('用户码').消费日期.max().reset_index() #找到每个用户的最近消费日期，构建df_R_value对象\n",
    "df_R_value.columns = ['用户码','最近购买日期'] #设定字段名\n",
    "df_R_value['R值'] = (df_R_value['最近购买日期'].max() - df_R_value['最近购买日期']).dt.days #计算最新日期与上次消费日期的天数\n",
    "df_user = pd.merge(df_user, df_R_value[['用户码','R值']], on='用户码') #把上次消费距最新日期的天数（R值）合并至df_user结构\n",
    "\n",
    "df_F_value = df_sales_3m.groupby('用户码').消费日期.count().reset_index() #计算每个用户消费次数，构建df_F_value对象\n",
    "df_F_value.columns = ['用户码','F值'] #设定字段名\n",
    "df_user = pd.merge(df_user, df_F_value[['用户码','F值']], on='用户码') #把消费频率(F值)整合至df_user结构\n",
    "\n",
    "df_M_value = df_sales_3m.groupby('用户码').总价.sum().reset_index() #计算每个用户三个月消费总额，构建df_M_value对象\n",
    "df_M_value.columns = ['用户码','M值'] #设定字段名\n",
    "df_user = pd.merge(df_user, df_M_value, on='用户码') #把消费总额整合至df_user结构\n",
    "df_user #显示用户表结构"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 构建标签"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>用户码</th>\n",
       "      <th>R值</th>\n",
       "      <th>F值</th>\n",
       "      <th>M值</th>\n",
       "      <th>年度LTV</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>15100</td>\n",
       "      <td>45</td>\n",
       "      <td>6</td>\n",
       "      <td>635.10</td>\n",
       "      <td>635.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15291</td>\n",
       "      <td>35</td>\n",
       "      <td>35</td>\n",
       "      <td>1329.95</td>\n",
       "      <td>4596.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>14688</td>\n",
       "      <td>6</td>\n",
       "      <td>85</td>\n",
       "      <td>1472.28</td>\n",
       "      <td>4449.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>15311</td>\n",
       "      <td>5</td>\n",
       "      <td>715</td>\n",
       "      <td>12711.66</td>\n",
       "      <td>58218.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>15862</td>\n",
       "      <td>89</td>\n",
       "      <td>64</td>\n",
       "      <td>354.23</td>\n",
       "      <td>659.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>365</th>\n",
       "      <td>15951</td>\n",
       "      <td>1</td>\n",
       "      <td>22</td>\n",
       "      <td>375.17</td>\n",
       "      <td>375.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>366</th>\n",
       "      <td>14745</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>240.60</td>\n",
       "      <td>1167.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>367</th>\n",
       "      <td>15724</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>103.65</td>\n",
       "      <td>212.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>368</th>\n",
       "      <td>15874</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>584.35</td>\n",
       "      <td>4330.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>369</th>\n",
       "      <td>15656</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>920.35</td>\n",
       "      <td>1425.90</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>370 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       用户码  R值   F值        M值     年度LTV\n",
       "0    15100  45    6    635.10    635.10\n",
       "1    15291  35   35   1329.95   4596.51\n",
       "2    14688   6   85   1472.28   4449.48\n",
       "3    15311   5  715  12711.66  58218.04\n",
       "4    15862  89   64    354.23    659.73\n",
       "..     ...  ..  ...       ...       ...\n",
       "365  15951   1   22    375.17    375.17\n",
       "366  14745   1    7    240.60   1167.16\n",
       "367  15724   0    5    103.65    212.30\n",
       "368  15874   0    5    584.35   4330.67\n",
       "369  15656   0   15    920.35   1425.90\n",
       "\n",
       "[370 rows x 5 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_user_1y = df_sales.groupby('用户码')['总价'].sum().reset_index() #计算每个用户整年消费总额，构建df_user_1y对象\n",
    "df_user_1y.columns = ['用户码','年度LTV'] #设定字段名\n",
    "df_user_1y.head() #显示头几行数据\n",
    "df_LTV = pd.merge(df_user, df_user_1y, on='用户码', how='left') #构建整体LTV训练数据集\n",
    "df_LTV #显示df_LTV"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 创建特征集和标签集"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>R值</th>\n",
       "      <th>F值</th>\n",
       "      <th>M值</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>45</td>\n",
       "      <td>6</td>\n",
       "      <td>635.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>35</td>\n",
       "      <td>35</td>\n",
       "      <td>1329.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6</td>\n",
       "      <td>85</td>\n",
       "      <td>1472.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>715</td>\n",
       "      <td>12711.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>89</td>\n",
       "      <td>64</td>\n",
       "      <td>354.23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   R值   F值        M值\n",
       "0  45    6    635.10\n",
       "1  35   35   1329.95\n",
       "2   6   85   1472.28\n",
       "3   5  715  12711.66\n",
       "4  89   64    354.23"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "X = df_LTV.drop(['用户码','年度LTV'],axis=1) #特征集\n",
    "X.head() #显示特征集"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      635.10\n",
       "1     4596.51\n",
       "2     4449.48\n",
       "3    58218.04\n",
       "4      659.73\n",
       "Name: 年度LTV, dtype: float64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "y = df_LTV['年度LTV'] #标签集\n",
    "y.head() #显示标签集"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 拆分训练集、验证集和测试集"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.model_selection import train_test_split\n",
    "\n",
    "# 拆分成训练集和测试集 \n",
    "X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state = 0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 创建模型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.linear_model import LinearRegression\n",
    "model_lr = LinearRegression()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 拆分K折"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "第1折验证集R2分数：0.5360997558766885\n",
      "第2折验证集R2分数：0.5597683702990446\n",
      "第3折验证集R2分数：0.502870818091667\n",
      "第4折验证集R2分数：-1.603181113150776\n",
      "第5折验证集R2分数：0.03969327865527228\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/lvan/anaconda3/lib/python3.6/site-packages/sklearn/linear_model/base.py:509: RuntimeWarning: internal gelsd driver lwork query error, required iwork dimension not returned. This is likely the result of LAPACK bug 0038, fixed in LAPACK 3.2.2 (released July 21, 2010). Falling back to 'gelss' driver.\n",
      "  linalg.lstsq(X, y)\n"
     ]
    }
   ],
   "source": [
    "from sklearn.model_selection import KFold #导入K折工具\n",
    "from sklearn.metrics import r2_score #导入R2分数评估工具\n",
    "kf5 = KFold(n_splits=5, shuffle=False) #5折验证\n",
    "i = 1 \n",
    "for train_index, test_index in kf5.split(df_LTV): \n",
    "    X_train = df_LTV.iloc[train_index].drop(['年度LTV'],axis=1) #训练集X\n",
    "    X_test = df_LTV.iloc[test_index].drop(['年度LTV'],axis=1) #验证集X\n",
    "    y_train = df_LTV.iloc[train_index]['年度LTV'] #训练集y\n",
    "    y_test = df_LTV.loc[test_index]['年度LTV'] #验证集y \n",
    "    model_lr.fit(X_train, y_train) #训练模型\n",
    "    print(f\"第{i}折验证集R2分数：{r2_score(y_test, model_lr.predict(X_test))}\") \n",
    "    i += 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 交叉验证"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "第1折验证集平均绝对误差： 3556.598343373126\n",
      "第2折验证集平均绝对误差： 1661.6416834373315\n",
      "第3折验证集平均绝对误差： 1368.9959442206114\n",
      "第4折验证集平均绝对误差： 1124.7941307578371\n",
      "第5折验证集平均绝对误差： 1832.964263004629\n"
     ]
    }
   ],
   "source": [
    "from sklearn.model_selection import cross_val_score # 导入交叉验证工具\n",
    "# from sklearn.metrics import mean_squared_error #平均绝对误差\n",
    "model_lr = LinearRegression() #线性回归模型\n",
    "scores = cross_val_score(model_lr, #线性回归\n",
    "                  X_train, #特征集\n",
    "                  y_train, #标签集\n",
    "                  cv=5, # 五折验证\n",
    "                  scoring = 'neg_mean_absolute_error') #平均绝对误差\n",
    "for i, score in enumerate(scores):\n",
    "    print(f\"第{i+1}折验证集平均绝对误差： {-score}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
